import { query } from '../../config/db.js';

/**
 * 目击线索数据模型
 */
export class Sighting {
  constructor(sightingData) {
    this.id = sightingData.id;
    this.post_id = sightingData.post_id;
    this.sighting_location = sightingData.sighting_location;
    this.sighting_time = sightingData.sighting_time;
    this.sighting_photo_url = sightingData.sighting_photo_url;
    this.description = sightingData.description;
    this.reporter_ip = sightingData.reporter_ip;
    this.is_verified = sightingData.is_verified;
    this.created_at = sightingData.created_at;
    
    // 关联数据
    this.post_title = sightingData.post_title;
    this.pet_name = sightingData.pet_name;
    this.pet_species = sightingData.pet_species;
    this.pet_color = sightingData.pet_color;
    this.owner_username = sightingData.owner_username;
  }

  /**
   * 创建新的目击线索
   * @param {Object} sightingData - 线索数据
   * @returns {Promise<Object>} 创建的线索信息
   */
  static async create(sightingData) {
    const { 
      post_id, 
      sighting_location, 
      sighting_time, 
      sighting_photo_url, 
      description, 
      reporter_ip 
    } = sightingData;
    
    const sql = `
      INSERT INTO sightings (post_id, sighting_location, sighting_time, sighting_photo_url, description, reporter_ip)
      VALUES (?, ?, ?, ?, ?, ?)
    `;
    
    try {
      const result = await query(sql, [
        post_id, 
        sighting_location, 
        sighting_time, 
        sighting_photo_url || null, 
        description || null, 
        reporter_ip || null
      ]);
      
      // 返回新创建的线索信息
      const newSighting = await Sighting.findById(result.insertId);
      return newSighting;
    } catch (error) {
      console.error('创建目击线索失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找线索
   * @param {number} id - 线索ID
   * @returns {Promise<Object|null>} 线索信息或null
   */
  static async findById(id) {
    const sql = `
      SELECT 
        s.*,
        CONCAT(pet.species, ' - ', pet.name) as post_title,
        pet.name as pet_name,
        pet.species as pet_species,
        pet.color as pet_color,
        u.username as owner_username
      FROM sightings s
      LEFT JOIN posts p ON s.post_id = p.id
      LEFT JOIN pets pet ON p.pet_id = pet.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE s.id = ?
    `;
    
    try {
      const results = await query(sql, [id]);
      return results.length > 0 ? new Sighting(results[0]) : null;
    } catch (error) {
      console.error('查找目击线索失败:', error);
      throw error;
    }
  }

  /**
   * 根据帖子ID查找线索列表
   * @param {number} postId - 帖子ID
   * @param {Object} options - 查询选项
   * @returns {Promise<Array>} 线索列表
   */
  static async findByPostId(postId, options = {}) {
    const { page = 1, limit = 10, includeUnverified = true } = options;
    const offset = (page - 1) * limit;
    
    let sql = `
      SELECT 
        s.*,
        CONCAT(pet.species, ' - ', pet.name) as post_title,
        pet.name as pet_name,
        pet.species as pet_species,
        pet.color as pet_color,
        u.username as owner_username
      FROM sightings s
      LEFT JOIN posts p ON s.post_id = p.id
      LEFT JOIN pets pet ON p.pet_id = pet.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE s.post_id = ?
    `;
    
    const params = [postId];

    if (!includeUnverified) {
      sql += ' AND s.is_verified = true';
    }

    sql += ` ORDER BY s.created_at DESC LIMIT ${parseInt(limit)} OFFSET ${parseInt(offset)}`;
    
    try {
      const results = await query(sql, params);
      return results.map(sighting => new Sighting(sighting));
    } catch (error) {
      console.error('查找帖子线索失败:', error);
      throw error;
    }
  }

  /**
   * 获取帖子的线索总数
   * @param {number} postId - 帖子ID
   * @param {boolean} includeUnverified - 是否包含未验证的线索
   * @returns {Promise<number>} 线索总数
   */
  static async countByPostId(postId, includeUnverified = true) {
    let sql = 'SELECT COUNT(*) as count FROM sightings WHERE post_id = ?';
    const params = [postId];

    if (!includeUnverified) {
      sql += ' AND is_verified = true';
    }
    
    try {
      const results = await query(sql, params);
      return results[0].count;
    } catch (error) {
      console.error('获取帖子线索总数失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有线索列表（管理员用）
   * @param {Object} filters - 筛选条件
   * @param {Object} options - 查询选项
   * @returns {Promise<Array>} 线索列表
   */
  static async findAll(filters = {}, options = {}) {
    const { is_verified, post_id } = filters;
    const { page = 1, limit = 10, orderBy = 'created_at', orderDir = 'DESC' } = options;
    const offset = (page - 1) * limit;
    
    let sql = `
      SELECT 
        s.*,
        CONCAT(pet.species, ' - ', pet.name) as post_title,
        pet.name as pet_name,
        pet.species as pet_species,
        pet.color as pet_color,
        u.username as owner_username
      FROM sightings s
      LEFT JOIN posts p ON s.post_id = p.id
      LEFT JOIN pets pet ON p.pet_id = pet.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE 1=1
    `;
    const params = [];

    if (is_verified !== undefined) {
      sql += ' AND s.is_verified = ?';
      params.push(is_verified);
    }

    if (post_id) {
      sql += ' AND s.post_id = ?';
      params.push(post_id);
    }

    sql += ` ORDER BY s.${orderBy} ${orderDir} LIMIT ${parseInt(limit)} OFFSET ${parseInt(offset)}`;

    try {
      const results = await query(sql, params);
      return results.map(sighting => new Sighting(sighting));
    } catch (error) {
      console.error('获取线索列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取线索总数
   * @param {Object} filters - 筛选条件
   * @returns {Promise<number>} 线索总数
   */
  static async count(filters = {}) {
    const { is_verified, post_id } = filters;
    
    let sql = 'SELECT COUNT(*) as count FROM sightings WHERE 1=1';
    const params = [];

    if (is_verified !== undefined) {
      sql += ' AND is_verified = ?';
      params.push(is_verified);
    }

    if (post_id) {
      sql += ' AND post_id = ?';
      params.push(post_id);
    }

    try {
      const results = await query(sql, params);
      return results[0].count;
    } catch (error) {
      console.error('获取线索总数失败:', error);
      throw error;
    }
  }

  /**
   * 更新线索验证状态
   * @param {number} id - 线索ID
   * @param {boolean} isVerified - 是否验证
   * @returns {Promise<Object|null>} 更新后的线索信息
   */
  static async updateVerificationStatus(id, isVerified) {
    const sql = `
      UPDATE sightings 
      SET is_verified = ?
      WHERE id = ?
    `;

    try {
      await query(sql, [isVerified, id]);
      return await Sighting.findById(id);
    } catch (error) {
      console.error('更新线索验证状态失败:', error);
      throw error;
    }
  }

  /**
   * 删除线索
   * @param {number} id - 线索ID
   * @returns {Promise<boolean>} 是否删除成功
   */
  static async delete(id) {
    const sql = 'DELETE FROM sightings WHERE id = ?';
    
    try {
      const result = await query(sql, [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除线索失败:', error);
      throw error;
    }
  }

  /**
   * 根据IP地址获取最近提交的线索数量（防止滥用）
   * @param {string} ip - IP地址
   * @param {number} hours - 小时数，默认24小时
   * @returns {Promise<number>} 线索数量
   */
  static async countRecentByIP(ip, hours = 24) {
    const sql = `
      SELECT COUNT(*) as count 
      FROM sightings 
      WHERE reporter_ip = ? 
      AND created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
    `;
    
    try {
      const results = await query(sql, [ip, hours]);
      return results[0].count;
    } catch (error) {
      console.error('获取IP线索数量失败:', error);
      throw error;
    }
  }

  /**
   * 获取线索统计信息
   * @returns {Promise<Object>} 统计信息
   */
  static async getStats() {
    const sql = `
      SELECT 
        COUNT(*) as total_sightings,
        COUNT(CASE WHEN is_verified = true THEN 1 END) as verified_sightings,
        COUNT(CASE WHEN is_verified = false THEN 1 END) as unverified_sightings,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_sightings,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as week_sightings,
        COUNT(CASE WHEN sighting_photo_url IS NOT NULL THEN 1 END) as sightings_with_photo
      FROM sightings
    `;

    try {
      const results = await query(sql);
      return results[0];
    } catch (error) {
      console.error('获取线索统计失败:', error);
      throw error;
    }
  }

  /**
   * 转换为JSON格式
   * @param {boolean} includeIP - 是否包含IP地址（仅管理员可见）
   * @returns {Object} 线索信息
   */
  toJSON(includeIP = false) {
    const result = {
      id: this.id,
      post_id: this.post_id,
      sighting_location: this.sighting_location,
      sighting_time: this.sighting_time,
      sighting_photo_url: this.sighting_photo_url,
      description: this.description,
      is_verified: this.is_verified,
      created_at: this.created_at,
      post_info: {
        title: this.post_title,
        pet_name: this.pet_name,
        pet_species: this.pet_species,
        pet_color: this.pet_color,
        owner_username: this.owner_username
      }
    };

    if (includeIP) {
      result.reporter_ip = this.reporter_ip;
    }

    return result;
  }
}
